﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Web;

namespace DataAccess.OLEDB
{
    #region oledbquery
    public class OleDbQuery
    {
        /// <summary>
        /// 实例化ACC数据库查詢类
        /// </summary>
        public OleDbQuery()
        {

        }
        /// <summary>
        /// 數據庫連接
        /// </summary>
        public OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+HttpContext.Current.Server.MapPath("~/App_Data/Data.mdb")+";");
        /// <summary>
        /// 打開數據庫連接
        /// </summary>
        public void ConnOpen()
        {
            //if (cn.State == ConnectionState.Closed)
            //{
            cn.Open();
            //}
        }
        /// <summary>
        /// 關閉數據庫連接
        /// </summary>
        public void ConnClose()
        {
            //if (cn.State != ConnectionState.Closed)
            //{
            cn.Close();
            //}
        }
        /// <summary>
        /// 錯訊
        /// </summary>
        public string errString = string.Empty;
        /// <summary>
        /// 影響行數
        /// </summary>
        public int ExcuteRowsCount = 0;
        /// <summary>
        /// 執行查旬
        /// </summary>
        /// <param name="sql">查詢語句</param>
        public void Excute(string sql)
        {
            Excute(sql, null);
        }
        /// <summary>
        /// 執行查旬
        /// </summary>
        /// <param name="sql">查詢語句</param>
        /// <param name="Parameters">參數數組</param>
        public void Excute(string sql, OleDbParameter[] Parameters)
        {
            ConnOpen();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            cmd.Connection = cn;
            cmd.CommandText = sql;
            if (Parameters != null)
            {
                foreach (OleDbParameter sp in Parameters)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                errString = ex.Message;
            }
            finally
            {
                ConnClose();

            }
        }
        /// <summary>
        /// 根據select查詢語句産生DataSet
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string selectCommandText)
        {
            DataSet ds = new DataSet();
            GetSqlDataAdapter(selectCommandText).Fill(ds);
            return ds;
        }
        /// <summary>
        /// 根據select查詢語句和參數數組産生DataTable
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <param name="Parameters">SqlParameter[]參數數組</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string selectCommandText, OleDbParameter[] Parameters)
        {
            DataSet dt = new DataSet();
            GetSqlDataAdapter(selectCommandText, Parameters).Fill(dt);
            return dt;

        }
        /// <summary>
        /// 根據select查詢語句産生DataTable
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string selectCommandText)
        {
            DataTable dt = new DataTable();
            GetSqlDataAdapter(selectCommandText).Fill(dt);
            return dt;
        }
        /// <summary>
        /// 根據select查詢語句和參數數組産生DataTable
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <param name="Parameters">SqlParameter[]參數數組</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string selectCommandText, OleDbParameter[] Parameters)
        {
            DataTable dt = new DataTable();
            GetSqlDataAdapter(selectCommandText, Parameters).Fill(dt);
            return dt;

        }
        /// <summary>
        /// 根據select查詢語句産生SqlDataAdapter
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <returns>SqlDataAdapter</returns>
        public OleDbDataAdapter GetSqlDataAdapter(string selectCommandText)
        {
            return GetSqlDataAdapter(selectCommandText, null);
        }
        /// <summary>
        /// 根據select查詢語句和參數數組産生SqlDataAdapter
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <param name="Parameters">SqlParameter[]參數數組</param>
        /// <returns>SqlDataAdapter</returns>
        public OleDbDataAdapter GetSqlDataAdapter(string selectCommandText, OleDbParameter[] Parameters)
        {
            ConnOpen();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cn;
            cmd.CommandText = selectCommandText;
            if (Parameters != null)
            {
                foreach (OleDbParameter sp in Parameters)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
            ConnClose();
            return sda;

        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="selectCommandText">查詢語句</param>
        /// <param name="Parameters">參數組</param>
        /// <returns>首行首列</returns>
        public object GetExecuteScalar(string selectCommandText, OleDbParameter[] Parameters)
        {
            ConnOpen();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cn;
            cmd.CommandText = selectCommandText;
            if (Parameters != null)
            {
                foreach (OleDbParameter sp in Parameters)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            object ss = cmd.ExecuteScalar();
            ConnClose();
            return ss;
        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="selectCommandText">查詢語句</param>
        /// <returns>首行首列</returns>
        public object GetExecuteScalar(string selectCommandText)
        {
            return GetExecuteScalar(selectCommandText, null);
        }
        /// <summary>
        /// 根據select查詢語句和參數數組産生SqlDataReader
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <param name="Parameters">SqlParameter[]參數數組</param>
        /// <returns>SqlDataReader</returns>
        public OleDbDataReader GetSqlDataReader(string selectCommandText, OleDbParameter[] Parameters)
        {
            ConnOpen();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cn;
            cmd.CommandText = selectCommandText;
            if (Parameters != null)
            {
                foreach (OleDbParameter sp in Parameters)
                {
                    cmd.Parameters.Add(sp);
                }
            }
            OleDbDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        /// <summary>
        /// 根據select查詢語句和參數數組産生SqlDataReader
        /// </summary>
        /// <param name="selectCommandText">select查詢語句</param>
        /// <returns>SqlDataReader</returns>
        public OleDbDataReader GetSqlDataReader(string selectCommandText)
        {
            return GetSqlDataReader(selectCommandText, null);
        }
    }
    #endregion
}
